package com.archetypus.projectschedule.common.data.involvements;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.archetypus.projectschedule.common.data.SQLServerDAOFactory;
import com.archetypus.projectschedule.common.dto.activities.Activity;
import com.archetypus.projectschedule.common.dto.activities.ActivityKey;
import com.archetypus.projectschedule.common.dto.involvements.Involvement;
import com.archetypus.projectschedule.common.dto.involvements.InvolvementKey;
import com.archetypus.projectschedule.common.dto.people.Person;
import com.archetypus.projectschedule.common.dto.people.PersonKey;
import com.archetypus.projectschedule.common.dto.projects.ProjectKey;
import com.archetypus.projectschedule.common.exceptions.ProjectSchedulerException;

public class SQLServerInvolvementDAO implements InvolvementDAO {

	@Override
	public void delete(Involvement involvement) throws ProjectSchedulerException {
		String sql = "DELETE FROM involvements WHERE project=? AND activity=? AND responsible=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, involvement.getInvolvementKey().getProject());
			stmt.setInt(2, involvement.getInvolvementKey().getActivity());
			stmt.setInt(3, involvement.getInvolvementKey().getResponsible());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not delete register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
	}

	@Override
	public Involvement find(Involvement involvement) throws ProjectSchedulerException {
		String sql = "SELECT project,activity,responsible,score,beginning,planned_end,real_end "+
						"FROM involvements WHERE project=? AND activity=? AND responsible=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Involvement retrievedInvolvement = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, involvement.getInvolvementKey().getProject());
			stmt.setInt(2, involvement.getInvolvementKey().getActivity());
			stmt.setInt(3, involvement.getInvolvementKey().getResponsible());
			rs = stmt.executeQuery();
			rs.next();
			retrievedInvolvement = new Involvement();
			retrievedInvolvement.setPlannedEnd(rs.getDate("planned_end"));
			retrievedInvolvement.setRealEnd(rs.getDate("real_end"));
			retrievedInvolvement.setScore(rs.getInt("score"));
			retrievedInvolvement.setBeginning(rs.getDate("beginning"));
			ProjectKey projectKey = new ProjectKey(rs.getInt("project"));
			ActivityKey activityKey = new ActivityKey(projectKey,rs.getInt("activity"));
			PersonKey personKey = new PersonKey(rs.getInt("responsible"));
			retrievedInvolvement.setInvolvementKey(new InvolvementKey(activityKey,personKey));
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return retrievedInvolvement;
	}

	@Override
	public Involvement insert(Involvement involvement) throws ProjectSchedulerException {
		String sql = "INSERT INTO involvements(project,activity,responsible,score,beginning,planned_end,real_end) "+
						"VALUES(?,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, involvement.getInvolvementKey().getProject());
			stmt.setInt(2, involvement.getInvolvementKey().getActivity());
			stmt.setInt(3, involvement.getInvolvementKey().getResponsible());
			stmt.setInt(4, involvement.getScore());
			stmt.setDate(5, (java.sql.Date)involvement.getBeginning());
			stmt.setDate(6, (java.sql.Date)involvement.getPlannedEnd());
			stmt.setDate(7, (java.sql.Date)involvement.getRealEnd());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not insert register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return involvement;
	}

	@Override
	public List<Involvement> list() throws ProjectSchedulerException {
		String sql = "SELECT project,activity,responsible,score,beginning,planned_end,real_end " +
						"FROM involvements";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Involvement> involvementsList = new ArrayList<Involvement>();
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Involvement i = new Involvement();
				i.setBeginning(rs.getDate("beginning"));
				i.setPlannedEnd(rs.getDate("planned_end"));
				i.setRealEnd(rs.getDate("real_end"));
				i.setScore(rs.getInt("score"));
				ProjectKey projectKey = new ProjectKey(rs.getInt("project"));
				ActivityKey activityKey = new ActivityKey(projectKey,rs.getInt("activity"));
				PersonKey personKey = new PersonKey(rs.getInt("responsible"));
				i.setInvolvementKey(new InvolvementKey(activityKey,personKey));
				involvementsList.add(i);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return involvementsList;
	}

	@Override
	public List<Involvement> listByActivity(Activity activity) throws ProjectSchedulerException {
		String sql = "SELECT project,activity,responsible,score,beginning,planned_end,real_end " +
						"FROM involvements WHERE activity=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Involvement> involvementsList = new ArrayList<Involvement>();

		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, activity.getActivityKey().getId());
			rs = stmt.executeQuery();
			while(rs.next()){
				Involvement i = new Involvement();
				i.setBeginning(rs.getDate("beginning"));
				i.setPlannedEnd(rs.getDate("planned_end"));
				i.setRealEnd(rs.getDate("real_end"));
				i.setScore(rs.getInt("score"));
				ProjectKey projectKey = new ProjectKey(rs.getInt("project"));
				ActivityKey activityKey = new ActivityKey(projectKey,rs.getInt("activity"));
				PersonKey personKey = new PersonKey(rs.getInt("responsible"));
				i.setInvolvementKey(new InvolvementKey(activityKey,personKey));
				involvementsList.add(i);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return involvementsList;
	}

	@Override
	public List<Involvement> listByPerson(Person person) throws ProjectSchedulerException {
		String sql = "SELECT project,activity,responsible,score,beginning,planned_end,real_end " +
						"FROM involvements WHERE responsible=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Involvement> involvementsList = new ArrayList<Involvement>();

		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, person.getPersonKey().getId());
			rs = stmt.executeQuery();
			while(rs.next()){
				Involvement i = new Involvement();
				i.setBeginning(rs.getDate("beginning"));
				i.setPlannedEnd(rs.getDate("planned_end"));
				i.setRealEnd(rs.getDate("real_end"));
				i.setScore(rs.getInt("score"));
				ProjectKey projectKey = new ProjectKey(rs.getInt("project"));
				ActivityKey activityKey = new ActivityKey(projectKey,rs.getInt("activity"));
				PersonKey personKey = new PersonKey(rs.getInt("responsible"));
				i.setInvolvementKey(new InvolvementKey(activityKey,personKey));
				involvementsList.add(i);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return involvementsList;
	}

	@Override
	public void update(Involvement involvement) throws ProjectSchedulerException {
		String sql = "UPDATE involvements SET beginning=?, planned_end=?, real_end=?, " +
					"score=? WHERE project=? AND activity=? AND responsible=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setDate(1, (java.sql.Date)involvement.getBeginning());
			stmt.setDate(2, (java.sql.Date)involvement.getPlannedEnd());
			stmt.setDate(3, (java.sql.Date)involvement.getRealEnd());
			stmt.setInt(4, involvement.getScore());
			stmt.setInt(5, involvement.getInvolvementKey().getProject());
			stmt.setInt(6, involvement.getInvolvementKey().getActivity());
			stmt.setInt(7, involvement.getInvolvementKey().getResponsible());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not update register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}

	}

}
